
CREATE EXTERNAL TABLE jms_dm.dm_customer_province_bill_amount_month_dt(
  pick_network_code string COMMENT '网点编码',
  pick_network_name string COMMENT '网点名称',
  first_franchisee_code string COMMENT '所属加盟商编码',
  first_franchisee_name string COMMENT '所属加盟商name',
  send_financial_center_code string COMMENT '所属财务中心code',
  send_financial_center_name string COMMENT '所属财务中心名称',
  date_time date COMMENT '日期',
  customer_code string COMMENT '客户Code',
  customer_name string COMMENT '客户名称',
  receiver_province_id string COMMENT '目的省份ID',
  receiver_province_name string COMMENT '目的省份名称',
  total_bill_count decimal(16,2) COMMENT '总票数',
  total_weight_count decimal(16,2) COMMENT '总重量（内部计费总量）',
  total_average_weight decimal(16,2) COMMENT '均重（总重量/总票数）',
  total_freight decimal(16,2) COMMENT '收入（总运费）',
  total_transfer_fee decimal(16,2) COMMENT '中转费',
  total_operation_fee decimal(16,2) COMMENT '操作费',
  total_base_dispatch_fee decimal(16,2) COMMENT '基础派费',
  total_piece_fee decimal(16,2) COMMENT '续重派费',
  total_sales_price decimal(16,2) COMMENT '面单费',
  total_bill_amount decimal(16,2) COMMENT '取件提成',
  total_prime_cost decimal(16,2) COMMENT '成本',
  total_profit decimal(16,2) COMMENT '利润',
  total_average_profit decimal(16,2) COMMENT '均利（利润/票数）',
  total_average_prime decimal(16,2) COMMENT '单票成本(成本/总票数)',
  zero_bill_count decimal(16,2) COMMENT 'w=0票数',
  zero_weight decimal(16,2) COMMENT 'w=0重量',
  zero_average_weight decimal(16,2) COMMENT 'w=0均重',
  zero_freight decimal(16,2) COMMENT 'w=0收入',
  zero_prime_cost decimal(16,2) COMMENT 'w=0成本',
  zero_profit decimal(16,2) COMMENT 'w=0利润',
  zero_average_profit decimal(16,2) COMMENT 'w=0均利（利润/票数）',
  one_bill_count decimal(16,2) COMMENT 'w>0 w<=0.5票数',
  one_weight decimal(16,2) COMMENT 'w>0 w<=0.5重量',
  one_average_weight decimal(16,2) COMMENT 'w>0 w<=0.5均重',
  one_freight decimal(16,2) COMMENT 'w>0 w<=0.5收入',
  one_prime_cost decimal(16,2) COMMENT 'w>0 w<=0.5成本',
  one_profit decimal(16,2) COMMENT 'w>0 w<=0.5利润',
  one_average_profit decimal(16,2) COMMENT 'w>0 w<=0.5均利（利润/票数）',
  two_bill_count decimal(16,2) COMMENT 'w>0.5 w<=3.2票数',
  two_weight decimal(16,2) COMMENT 'w>0.5 w<=3.2重量',
  two_average_weight decimal(16,2) COMMENT 'w>0.5 w<=3.2均重',
  two_freight decimal(16,2) COMMENT 'w>0.5 w<=3.2收入',
  two_prime_cost decimal(16,2) COMMENT 'w>0.5 w<=3.2成本',
  two_profit decimal(16,2) COMMENT 'w>0.5 w<=3.2利润',
  two_average_profit decimal(16,2) COMMENT 'w>0.5 w<=3.2均利（利润/票数）',
  three_bill_count decimal(16,2) COMMENT 'w>3.2 w<=5.2票数',
  three_weight decimal(16,2) COMMENT 'w>3.2 w<=5.2重量',
  three_average_weight decimal(16,2) COMMENT 'w>3.2 w<=5.2均重',
  three_freight decimal(16,2) COMMENT 'w>3.2 w<=5.2收入',
  three_prime_cost decimal(16,2) COMMENT 'w>3.2 w<=5.2成本',
  three_profit decimal(16,2) COMMENT 'w>3.2 w<=5.2利润',
  three_average_profit decimal(16,2) COMMENT 'w>3.2 w<=5.2均利（利润/票数）',
  four_bill_count decimal(16,2) COMMENT 'w>5.2 w<=10.3票数',
  four_weight decimal(16,2) COMMENT 'w>5.2 w<=10.3重量',
  four_average_weight decimal(16,2) COMMENT 'w>5.2 w<=10.3均重',
  four_freight decimal(16,2) COMMENT 'w>5.2 w<=10.3收入',
  four_prime_cost decimal(16,2) COMMENT 'w>5.2 w<=10.3成本',
  four_profit decimal(16,2) COMMENT 'w>5.2 w<=10.3利润',
  four_average_profit decimal(16,2) COMMENT 'w>5.2 w<=10.3均利（利润/票数）',
  five_bill_count decimal(16,2) COMMENT 'w>10.3票数',
  five_weight decimal(16,2) COMMENT 'w>10.3重量',
  five_average_weight decimal(16,2) COMMENT 'w>10.3均重',
  five_freight decimal(16,2) COMMENT 'w>10.3收入',
  five_prime_cost decimal(16,2) COMMENT 'w>10.3成本',
  five_profit decimal(16,2) COMMENT 'w>10.3利润',
  five_average_profit decimal(16,2) COMMENT 'w>10.3均利（利润/票数）',
  six_bill_count decimal(16,2) COMMENT '0.5＜W≤1票数',
  six_weight decimal(16,2) COMMENT '0.5＜W≤1重量',
  six_average_weight decimal(16,2) COMMENT '0.5＜W≤1均重',
  six_freight decimal(16,2) COMMENT '0.5＜W≤1收入',
  six_prime_cost decimal(16,2) COMMENT '0.5＜W≤1成本',
  six_profit decimal(16,2) COMMENT '0.5＜W≤1利润',
  six_average_profit decimal(16,2) COMMENT '0.5＜W≤1均利',
  seven_bill_count decimal(16,2) COMMENT '1＜W≤2票数',
  seven_weight decimal(16,2) COMMENT '1＜W≤2重量',
  seven_average_weight decimal(16,2) COMMENT '1＜W≤2均重',
  seven_freight decimal(16,2) COMMENT '1＜W≤2收入',
  seven_prime_cost decimal(16,2) COMMENT '1＜W≤2成本',
  seven_profit decimal(16,2) COMMENT '1＜W≤2利润',
  seven_average_profit decimal(16,2) COMMENT '1＜W≤2均利',
  eight_bill_count decimal(16,2) COMMENT '2＜W≤3.2票数',
  eight_weight decimal(16,2) COMMENT '2＜W≤3.2重量',
  eight_average_weight decimal(16,2) COMMENT '2＜W≤3.2均重',
  eight_freight decimal(16,2) COMMENT '2＜W≤3.2收入',
  eight_prime_cost decimal(16,2) COMMENT '2＜W≤3.2成本',
  eight_profit decimal(16,2) COMMENT '2＜W≤3.2利润',
  eight_average_profit decimal(16,2) COMMENT '2＜W≤3.2均利',
  nine_bill_count decimal(16,2) COMMENT '5.2＜W≤20票数',
  nine_weight decimal(16,2) COMMENT '5.2＜W≤20重量',
  nine_average_weight decimal(16,2) COMMENT '5.2＜W≤20均重',
  nine_freight decimal(16,2) COMMENT '5.2＜W≤20收入',
  nine_prime_cost decimal(16,2) COMMENT '5.2＜W≤20成本',
  nine_profit decimal(16,2) COMMENT '5.2＜W≤20利润',
  nine_average_profit decimal(16,2) COMMENT '5.2＜W≤20均利',
  ten_bill_count decimal(16,2) COMMENT 'W>20票数',
  ten_weight decimal(16,2) COMMENT 'W>20重量',
  ten_average_weight decimal(16,2) COMMENT 'W>20均重',
  ten_freight decimal(16,2) COMMENT 'W>20收入',
  ten_prime_cost decimal(16,2) COMMENT 'W>20成本',
  ten_profit decimal(16,2) COMMENT 'W>20利润',
  ten_average_profit decimal(16,2) COMMENT 'W>20均利',
  m_avg_total_bill_count decimal(16,2) comment '月日均件量',
  if_in_province_bill decimal(1,0) comment '是否省内件'
  )
COMMENT '网点客户--目的省份财务表-月表'
PARTITIONED BY (
  dt string COMMENT '更新日期 (yyyy-MM-dd)')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://yl-bg-hdp/dw/hive/jms_dm.db/external/dm_customer_province_bill_amount_month_dt'
TBLPROPERTIES (
  'bucketing_version'='2',
  'discover.partitions'='false',
  'last_modified_by'='hive',
  'last_modified_time'='1680874390',
  'parquet.column.index.access'='true',
  'spark.sql.create.version'='2.2 or prior',
  'spark.sql.sources.schema.numPartCols'='1',
  'spark.sql.sources.schema.numParts'='3',
  'spark.sql.sources.schema.part.0'='{"type":"struct","fields":[{"name":"pick_network_code","type":"string","nullable":true,"metadata":{"comment":"网点编码"}},{"name":"pick_network_name","type":"string","nullable":true,"metadata":{"comment":"网点名称"}},{"name":"first_franchisee_code","type":"string","nullable":true,"metadata":{"comment":"所属加盟商编码"}},{"name":"first_franchisee_name","type":"string","nullable":true,"metadata":{"comment":"所属加盟商name"}},{"name":"send_financial_center_code","type":"string","nullable":true,"metadata":{"comment":"所属财务中心code"}},{"name":"send_financial_center_name","type":"string","nullable":true,"metadata":{"comment":"所属财务中心名称"}},{"name":"date_time","type":"date","nullable":true,"metadata":{"comment":"日期"}},{"name":"customer_code","type":"string","nullable":true,"metadata":{"comment":"客户Code"}},{"name":"customer_name","type":"string","nullable":true,"metadata":{"comment":"客户名称"}},{"name":"receiver_province_id","type":"string","nullable":true,"metadata":{"comment":"目的省份ID"}},{"name":"receiver_province_name","type":"string","nullable":true,"metadata":{"comment":"目的省份名称"}},{"name":"total_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"总票数"}},{"name":"total_weight_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"总重量（内部计费总量）"}},{"name":"total_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"均重（总重量/总票数）"}},{"name":"total_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"收入（总运费）"}},{"name":"total_transfer_fee","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"中转费"}},{"name":"total_operation_fee","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"操作费"}},{"name":"total_base_dispatch_fee","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"基础派费"}},{"name":"total_piece_fee","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"续重派费"}},{"name":"total_sales_price","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"面单费"}},{"name":"total_bill_amount","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"取件提成"}},{"name":"total_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"成本"}},{"name":"total_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"利润"}},{"name":"total_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"均利（利润/票数）"}},{"name":"total_average_prime","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"单票成本(成本/总票数)"}},{"name":"zero_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w=0票数"}},{"name":"zero_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w=0重量"}},{"name":"zero_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w=0均重"}},{"name":"zero_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w=0收入"}},{"name":"zero_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w=0成本"}},{"name":"zero_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w=0利润"}},{"name":"zero_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w=0均利（利润/票数）"}},{"name":"one_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0 w<=0.5票数"}},{"name":"one_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0 w<=0.5重量"}},{"name":"one_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0 w<=0.5均重"}},{"name":"one_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0 w<=0.5收入"}},{"name":"one_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0 w<=0.5成本"}},{"name":"one_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0 w<=0.5利润"}},{"name":"one_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0 w<=0.5均利（利润/票数）"}},{"name":"two_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0.5 w<=3.2票数"}},{"name":"two_weight","',
  'spark.sql.sources.schema.part.1'='type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0.5 w<=3.2重量"}},{"name":"two_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0.5 w<=3.2均重"}},{"name":"two_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0.5 w<=3.2收入"}},{"name":"two_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0.5 w<=3.2成本"}},{"name":"two_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0.5 w<=3.2利润"}},{"name":"two_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>0.5 w<=3.2均利（利润/票数）"}},{"name":"three_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>3.2 w<=5.2票数"}},{"name":"three_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>3.2 w<=5.2重量"}},{"name":"three_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>3.2 w<=5.2均重"}},{"name":"three_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>3.2 w<=5.2收入"}},{"name":"three_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>3.2 w<=5.2成本"}},{"name":"three_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>3.2 w<=5.2利润"}},{"name":"three_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>3.2 w<=5.2均利（利润/票数）"}},{"name":"four_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>5.2 w<=10.3票数"}},{"name":"four_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>5.2 w<=10.3重量"}},{"name":"four_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>5.2 w<=10.3均重"}},{"name":"four_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>5.2 w<=10.3收入"}},{"name":"four_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>5.2 w<=10.3成本"}},{"name":"four_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>5.2 w<=10.3利润"}},{"name":"four_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>5.2 w<=10.3均利（利润/票数）"}},{"name":"five_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>10.3票数"}},{"name":"five_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>10.3重量"}},{"name":"five_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>10.3均重"}},{"name":"five_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>10.3收入"}},{"name":"five_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>10.3成本"}},{"name":"five_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>10.3利润"}},{"name":"five_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"w>10.3均利（利润/票数）"}},{"name":"six_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"0.5＜W≤1票数"}},{"name":"six_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"0.5＜W≤1重量"}},{"name":"six_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"0.5＜W≤1均重"}},{"name":"six_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"0.5＜W≤1收入"}},{"name":"six_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"0.5＜W≤1成本"}},{"name":"six_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"0.5＜W≤1利润"}},{"name":"six_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"0.5＜W≤1均利"}},{"name":"seven_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"1＜W≤2票数"}},{"name":"seven_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"1＜W≤2重量"}},{"name":"seven_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"1＜W≤2均重"}},{"name":"seven_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"1＜W≤2收入"}},{"name":"seven_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comme',
  'spark.sql.sources.schema.part.2'='nt":"1＜W≤2成本"}},{"name":"seven_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"1＜W≤2利润"}},{"name":"seven_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"1＜W≤2均利"}},{"name":"eight_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"2＜W≤3.2票数"}},{"name":"eight_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"2＜W≤3.2重量"}},{"name":"eight_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"2＜W≤3.2均重"}},{"name":"eight_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"2＜W≤3.2收入"}},{"name":"eight_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"2＜W≤3.2成本"}},{"name":"eight_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"2＜W≤3.2利润"}},{"name":"eight_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"2＜W≤3.2均利"}},{"name":"nine_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"5.2＜W≤20票数"}},{"name":"nine_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"5.2＜W≤20重量"}},{"name":"nine_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"5.2＜W≤20均重"}},{"name":"nine_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"5.2＜W≤20收入"}},{"name":"nine_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"5.2＜W≤20成本"}},{"name":"nine_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"5.2＜W≤20利润"}},{"name":"nine_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"5.2＜W≤20均利"}},{"name":"ten_bill_count","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"W>20票数"}},{"name":"ten_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"W>20重量"}},{"name":"ten_average_weight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"W>20均重"}},{"name":"ten_freight","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"W>20收入"}},{"name":"ten_prime_cost","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"W>20成本"}},{"name":"ten_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"W>20利润"}},{"name":"ten_average_profit","type":"decimal(16,2)","nullable":true,"metadata":{"comment":"W>20均利"}},{"name":"dt","type":"string","nullable":true,"metadata":{"comment":"更新日期 (yyyy-MM-dd)"}}]}',
  'spark.sql.sources.schema.partCol.0'='dt',
  'transient_lastDdlTime'='1680877090');


ALTER TABLE jms_dm.dm_customer_province_bill_amount_month_dt add COLUMNS(
cust_level string COMMENT "客户等级"
);

ALTER TABLE jms_dm.dm_customer_province_bill_amount_month_dt add COLUMNS(
lm_cust_level string COMMENT "上月客户等级"
);